#!/usr/bin/env python
# -*- coding: utf-8 -*-
# Time          : 2024/4/30 17:36
# File          : sql_insert_choice.py
# @Author       : MingTai
# @Department   : automation_qa
import random

from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker

from app.business.mysql_init.init_sql_alchemy import db


# 创建用户表


class UserDB(db.Model):
    __tablename__ = "user"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(16), unique=True)
    email = db.Column(db.String(32), unique=True)
    password = db.Column(db.String(16))
    role_id = db.Column(db.Integer)

    def to_dict(self):
        """将UserDB实例的信息转换为字典格式，便于进一步的JSON化。"""
        user_data = {
            'id': self.id,
            'name': self.name,
            'email': self.email,
            'password': self.password,  # 注意安全性
            'role_id': self.role_id
        }
        return user_data


def insert_user_if_not_none(username, email=None):
    # 创建一个字典，仅包含非None的字段
    data = {}
    if username:
        data['name'] = username
    if email:
        data['email'] = email

    # 使用字典展开语法将字段作为关键字参数传递给模型
    new_user = UserDB(**data)
    new_user.role_id = random.randrange(1, 30)
    new_user.age = random.randrange(20, 50)
    new_user.password = random.randrange(100000, 999999)

    # 添加到session并提交
    db.session.add(new_user)
    try:
        db.session.commit()
        return True
    except Exception as e:
        print("An error occurred while inserting the user:", e)
        db.session.rollback()
        return False


# 执行原生的SQL语句，一般不使用，执行简单点的SQL会快点
def db_session_query_demo():
    engine = create_engine(f'mysql+pymysql://root:123456@127.0.0.1:3306/flask_demo')
    db_session = scoped_session(sessionmaker(autoflush=False, bind=engine))
    sql_11 = 'select * from user where role_id = 6 '
    aa = db_session.execute(sql_11)
    print(aa.fetchall())
    print(aa)


if __name__ == '__main__':
    db_session_query_demo()


# 批量查询数据库
def select_user():
    id_list = [1, 3, 5, 7, 9]
    res = UserDB.query.filter(UserDB.id.in_(id_list)).all()
    user_list = []
    for user in res:
        user_list.append(user.to_dict())
        print(user.to_dict())

    # 两个用法返回值相等 res = UserDB.query.filter(UserDB.id.in_(id_list)).all()
    aaa = db.session.query(UserDB).filter(UserDB.id.in_(id_list)).all()
    db.session.commit()
    print(aaa)
    return user_list


# 批量查询数据库
def update_user():
    id_list_update = ['123@123.com', 'lis@163.com']
    id_list_update_many = [3, 8]

    # 返回值是更新数量
    update_one = db.session.query(UserDB).filter(UserDB.email.in_(id_list_update)).update({'email': 'one56789@123.com'},
                                                                                          synchronize_session=False)
    db.session.commit()

    update_many = db.session.query(UserDB).filter(UserDB.role_id.in_(id_list_update_many)).update({
        'email': '123_many@123.com',
        'password': 'many_123'
    }, synchronize_session=False)
    db.session.commit()
    print(update_one)
    print(update_many)
    return None
